﻿using System;
using System.Data;
using System.Linq;
using System.Data.Linq;
using System.Collections.Generic;
using System.Linq.Dynamic;
using System.Web.UI;
using System.Web.UI.WebControls;

using TempEmployees.DAL;
using Infrastructure.NetExtensions;
using TempEmployees.BLL.EntitiesExtensions;
using TempEmployees.BLL.Budgets;

namespace TempEmployees.Web
{
    using COLUMNS = WebConfigData;
    using TempEmployees.BLL.Database;

    /// <summary>
    /// This class is implementing methods that queries the DB.
    /// </summary>
    public static class DbServices
    {
        // DB Data Context
        public static TempEmpDatabaseDataContext db
        {
            get
            {
                return new TempEmpDatabaseDataContext();
            }
        }
        
        // icons
        private static readonly string COLUMN_ICON = "ICON_URL";
        // tool tip
        private static readonly string COLUMN_TOOL_TIP = "TOOL_TIP";
        // status id
        public static readonly string COLUMN_STATUS_ID = "STATUS_ID";
        // lab id
        private static readonly string COLUMN_FIELD_LABS_ID = "DeptLabID";

        // DropDown Dictionaries
        public const string EVERYTHING = "הכל";
        public static readonly string ELSE = "אחר";
        public static readonly string CHOOSE_EMPLOYER = "בחר מעסיק";
        public static readonly string CHOOSE_BUDGET = "בחר תקציב";
        public static readonly string CHOOSE_ROLE = "בחר תפקיד";

        public static void SetDropDownBudgetTypes(DropDownList list)
        {
            list.Items.Clear();
            list.Items.Add(new ListItem(EVERYTHING, "0"));
            foreach (BudgetType budgetType in db.BudgetTypes)
            {
                list.Items.Add(new ListItem(budgetType.Name, budgetType.ID.ToString()));
            }  
        }

        public static void SetDropDownUsers(DropDownList list, string optional = EVERYTHING)
        {
            list.Items.Clear();
            list.Items.Add(new ListItem(optional, "0"));
            List<User> users = (
                from user in db.Users
                where (user.Roles & (int)DbConstants.Roles.Employer) == (int)DbConstants.Roles.Employer
                select user).ToList();
            users.ForEach(user =>
            {
                list.Items.Add(new ListItem(user.ToString(), user.ID.ToString()));
            });
        }

        public static void SetDropDownStatus(DropDownList list)
        {
            list.Items.Clear();
            list.Items.Add(new ListItem(EVERYTHING, "0"));
            List<Status> statusList = DbQuery.GetTableRows<Status>(new Filters(), "Stage");
            foreach (Status status in statusList)
            {
                list.Items.Add(new ListItem(status.Name, status.ID.ToString()));
            }
        }

        public static void SetDropDownFaculty(DropDownList list)
        {
            list.Items.Clear();
            list.Items.Add(new ListItem(EVERYTHING, "0"));
            foreach (Faculty faculty in db.Faculties)
            {
                list.Items.Add(new ListItem(faculty.Name, faculty.ID.ToString()));
            }
        }

        public static void SetDropDownSemesters(DropDownList list, bool setSemester)
        {
            list.Items.Clear();
            list.Items.Add(new ListItem(EVERYTHING, "0"));
            List<Semester> semList = DbQuery.GetTableRows<Semester>(new Filters(), "StartDate");
            foreach (Semester sem in semList)
            {
                list.Items.Add(new ListItem(sem.Name, sem.ID.ToString()));
            }
            if (setSemester) list.SelectedValue = ESemester.GetRelevantSemesterId().ToString();
        }

        public static void SetDropDownDepLab(DropDownList list)
        {
            list.Items.Clear();
            list.Items.Add(new ListItem(EVERYTHING, "0"));
            foreach (Department dep in db.Departments)
            {
                list.Items.Add(new ListItem(dep.Name, dep.ID.ToString()));
            }
        }

        public static void SetDropDownBudgets(DropDownList list, int budgetTypeId)
        {
            list.Items.Clear();
            List<Budget> budgetList = DbQuery.GetTableRows<Budget>("TypeID", budgetTypeId);
            list.Items.Add(new ListItem(CHOOSE_BUDGET, "-1"));
            foreach (Budget budget in budgetList)
            {
                list.Items.Add(new ListItem(budget.ID.ToString(), budget.ID.ToString()));
            }
        }
        public static void SetDropDownBudgets(DropDownList list, string userId, int budgetTypeId)
        {
            list.Items.Clear();
            Filters filters = new Filters();
            filters.Add("UserID", userId);
            filters.Add("Budget.TypeID", budgetTypeId);
            List<AssignedBudget> budgetList = DbQuery.GetTableRows<AssignedBudget>(filters);
            foreach (AssignedBudget budget in budgetList)
            {
                list.Items.Add(new ListItem(budget.BudgetID, budget.BudgetID));
            }
            list.Items.Add(new ListItem(ELSE, "-1"));
        }

        public static void SetDropDownRoles(DropDownList list, User user)
        {
            list.Items.Clear();
            list.Items.Add(new ListItem(CHOOSE_ROLE, "0"));
            foreach (DbConstants.Roles role in EUser.GetUserRoles(user).Roles)
            {
                list.Items.Add(new ListItem(((DbConstants.Roles)role).HebrewName(), ((int)role).ToString()));
            }
        }

        public static bool FillAssignmentsGridView(DataTable dt, Filters filters)
        {
            dt.Clear();
            
            // add columns to the table
            if (dt.Columns.Count == 0)
            {
                dt.Columns.Add(COLUMN_ICON, typeof(string));
                dt.Columns.Add(/*COLUMN_ASSIGNMENT_ID*/"#", typeof(int));
                dt.Columns.Add(COLUMN_TOOL_TIP, typeof(string));
                dt.Columns.Add(COLUMNS.FIELD_EMPLOYEE_NAME, typeof(string));
                dt.Columns.Add(COLUMNS.FIELD_EMPLOYEE_ID, typeof(string));
                dt.Columns.Add(COLUMNS.FIELD_DEPARTMENT_NAME, typeof(string));
                dt.Columns.Add(COLUMNS.FIELD_ASSIGNMENT_STATUS, typeof(string));
                dt.Columns.Add(COLUMN_STATUS_ID, typeof(int));
                dt.Columns.Add(COLUMNS.FIELD_ASSIGNMENT_BUDGET_TYPE, typeof(string));
                dt.Columns.Add(COLUMNS.FIELD_ASSIGNMENT_START_DATE, typeof(DateTime));
                dt.Columns.Add(COLUMNS.FIELD_ASSIGNMENT_END_DATE, typeof(DateTime));
                dt.Columns.Add(COLUMNS.FIELD_ASSIGNMENT_SALARY, typeof(string));
                dt.Columns.Add(COLUMNS.FIELD_ASSIGNMENT_HOURS, typeof(int));
            }

            // query the DB
            List<Assignment> assignmentList = DbQuery.GetTableRows<Assignment>(filters, "StatusID , StartDate");
            
            // fill the DataTable
            DataRow row; 
            foreach (Assignment assignment in assignmentList)
            {
                row = dt.AddNewRow();
                row[COLUMN_ICON] = SetIconOrTip(assignment.StatusID, IconOrTip.ICON);
                row["#"] = assignment.ID;
                row[COLUMN_TOOL_TIP] = SetIconOrTip(assignment.StatusID, IconOrTip.TIP);
                row[COLUMNS.FIELD_EMPLOYEE_NAME] = assignment.Employee.ToString();
                row[COLUMNS.FIELD_EMPLOYEE_ID] = assignment.EmployeeID;
                row[COLUMNS.FIELD_DEPARTMENT_NAME] = assignment.Department.Name;
                row[COLUMNS.FIELD_ASSIGNMENT_STATUS] = assignment.Status.Name;
                row[COLUMN_STATUS_ID] = assignment.StatusID;
                row[COLUMNS.FIELD_ASSIGNMENT_BUDGET_TYPE] = ((DbConstants.BudgetType)assignment.Budget.TypeID).HebrewName();
                row[COLUMNS.FIELD_ASSIGNMENT_START_DATE] = assignment.StartDate;
                row[COLUMNS.FIELD_ASSIGNMENT_END_DATE] = assignment.EndDate;
                row[COLUMNS.FIELD_ASSIGNMENT_SALARY] = decimal.Round((assignment.Salary != 0) ? assignment.Salary : EBaseSalary.CurrentBaseSalary.Salary, 2);
                row[COLUMNS.FIELD_ASSIGNMENT_HOURS] = assignment.MonthlyHours;
            }

            return (dt.Rows.Count > 0);
        }

        public static bool FillEmployeeGridView(DataTable dt, Filters filters)
        {
            dt.Clear();
            
            // add columns to the table
            if (dt.Columns.Count == 0)
            {
                dt.Columns.Add(COLUMNS.FIELD_EMPLOYEE_ID, typeof(string));
                dt.Columns.Add(COLUMNS.FIELD_EMPLOYEE_NAME, typeof(string));
                dt.Columns.Add(COLUMNS.FIELD_EMPLOYEE_FACULTY, typeof(string));
                dt.Columns.Add(COLUMNS.FIELD_EMPLOYEE_START_SEMESTER, typeof(string));
                dt.Columns.Add(COLUMNS.FIELD_LATEST_ASSIGNMENT, typeof(string));
                dt.Columns.Add(COLUMNS.FIELD_ASSIGNMENT_BUDGET_TYPE, typeof(string));
            }

            if (filters.Count == 0) // bring all employees from Employee table
            {
                // query the DB
                List<Employee> employeeList = DbQuery.GetTableRows<Employee>(filters, "ID");

                // fill the DataTable
                DataRow row;
                foreach (Employee employee in employeeList)
                {
                    Assignment latest = employee.LatestAssignment;

                    row = dt.AddNewRow();
                    row[COLUMNS.FIELD_EMPLOYEE_ID] = employee.ID;
                    row[COLUMNS.FIELD_EMPLOYEE_NAME] = employee.ToString();
                    row[COLUMNS.FIELD_EMPLOYEE_FACULTY] = employee.Faculty.Name;
                    row[COLUMNS.FIELD_EMPLOYEE_START_SEMESTER] = employee.Semester.Name;
                    row[COLUMNS.FIELD_LATEST_ASSIGNMENT] = (latest == null) ? string.Empty : latest.Semester.Name;
                    row[COLUMNS.FIELD_ASSIGNMENT_BUDGET_TYPE] = (latest == null) ? string.Empty : ((DbConstants.BudgetType)latest.Budget.TypeID).HebrewName();
                }
            }
            else // bring relevant employees (query Assignment table)
            {
                // query the DB
                List<Assignment> assignmentList = DbQuery.GetTableRows<Assignment>(filters, "EmployeeID");

                // fill the DataTable
                DataRow row;
                List<string> inserted = new List<string>();
                foreach (Assignment assignment in assignmentList)
                {
                    if (inserted.Contains(assignment.EmployeeID)) continue;
                    inserted.Add(assignment.EmployeeID);
                    row = dt.AddNewRow();
                    row[COLUMNS.FIELD_EMPLOYEE_ID] = assignment.Employee.ID;
                    row[COLUMNS.FIELD_EMPLOYEE_NAME] = assignment.Employee.ToString();
                    row[COLUMNS.FIELD_EMPLOYEE_FACULTY] = assignment.Employee.Faculty.Name;
                    row[COLUMNS.FIELD_EMPLOYEE_START_SEMESTER] = assignment.Employee.Semester.Name;
                    row[COLUMNS.FIELD_LATEST_ASSIGNMENT] = assignment.Semester.Name;
                    row[COLUMNS.FIELD_ASSIGNMENT_BUDGET_TYPE] = ((DbConstants.BudgetType)assignment.Budget.TypeID).HebrewName();
                }
            }
            if (dt.Rows.Count == 0)
            {
                return false;
            }
            return true;
        }

        public static bool FillDepartmentsGridView(DataTable dt, Filters filters)
        {
            dt.Clear();

            dt.Columns.Add(COLUMN_FIELD_LABS_ID, typeof(int));
            dt.Columns.Add(COLUMNS.FIELD_LAB_NAME, typeof(string));
            dt.Columns.Add(COLUMNS.FIELD_INITIAL_BUDGET, typeof(double));
            dt.Columns.Add(COLUMNS.FIELD_STATIC_BALANCE, typeof(double));
            dt.Columns.Add(COLUMNS.FIELD_CURRENT_BALANCE, typeof(double));
            
            List<DepartmentsInfo> departmentList = DbQuery.GetTableRows<DepartmentsInfo>(filters);

            foreach (DepartmentsInfo departmentInfo in departmentList)
            {
                DepartmentBudget budget = new DepartmentBudget(departmentInfo);
                DataRow row = dt.AddNewRow();
                row[COLUMN_FIELD_LABS_ID] = departmentInfo.DepartmentID;
                row[COLUMNS.FIELD_LAB_NAME] = departmentInfo.Department.Name;
                row[COLUMNS.FIELD_INITIAL_BUDGET] = budget.GetInitialBudget();
                row[COLUMNS.FIELD_STATIC_BALANCE] = budget.GetStaticBalance();
                row[COLUMNS.FIELD_CURRENT_BALANCE] = budget.GetCurrentBalance();
            }

            return true;
        }

        public static bool FillAssignmentStatusGridView(DataTable dt, Filters filters)
        {
            dt.Clear();

            dt.Columns.Add(/*COLUMN_ASSIGNMENT_ID*/"#", typeof(int));
            dt.Columns.Add(COLUMNS.FIELD_EMPLOYEE_ID, typeof(string));
            dt.Columns.Add(COLUMNS.FIELD_EMPLOYEE_NAME, typeof(string));
            dt.Columns.Add(COLUMNS.FIELD_ASSIGNMENT_BUDGET_TYPE, typeof(string));
            dt.Columns.Add(COLUMNS.FIELD_INITIAL_HOURS_AMOUNT, typeof(double));
            dt.Columns.Add(COLUMNS.FIELD_CURRENT_HOURS, typeof(double));
            dt.Columns.Add(COLUMNS.FIELD_INITIAL_BUDGET, typeof(double));
            dt.Columns.Add(COLUMNS.FIELD_CURRENT_BALANCE, typeof(double));

            List<Assignment> assignments = DbQuery.GetTableRows<Assignment>(filters);
            Semester semester = ESemester.GetSemesterByID(int.Parse(filters["SemesterID"].ToString()));

            foreach (Assignment assignment in assignments)
            {
                AssignmentBalance balance = new AssignmentBalance(assignment, semester);
                DataRow row = dt.AddNewRow();
                row["#"] = assignment.ID;
                row[COLUMNS.FIELD_EMPLOYEE_ID] = assignment.Employee.ID;
                row[COLUMNS.FIELD_EMPLOYEE_NAME] = assignment.Employee.ToString();
                row[COLUMNS.FIELD_ASSIGNMENT_BUDGET_TYPE] = assignment.BudgetID;
                row[COLUMNS.FIELD_INITIAL_HOURS_AMOUNT] = balance.GetStaticHours();
                row[COLUMNS.FIELD_CURRENT_HOURS] = balance.GetCurrentHoursBalance();
                row[COLUMNS.FIELD_INITIAL_BUDGET] = balance.GetStaticCost();
                row[COLUMNS.FIELD_CURRENT_BALANCE] = balance.GetCurrentBalance();
            }

            return (dt.Rows.Count > 0);
        }

        private enum IconOrTip { ICON , TIP }
        private static string SetIconOrTip(int statusId , IconOrTip what)
        {
            DbConstants.Status status = (DbConstants.Status)statusId;
            switch (status)
            {
                case DbConstants.Status.Active:
                    return (what == IconOrTip.ICON) ? WebConfigData.ICON_ACTIVE : WebConfigData.TIP_ACTIVE;
                case DbConstants.Status.Approved:
                    return (what == IconOrTip.ICON) ? WebConfigData.ICON_APPROVED : WebConfigData.TIP_APPROVED;
                case DbConstants.Status.Pending:
                    return (what == IconOrTip.ICON) ? WebConfigData.ICON_PENDING : WebConfigData.TIP_PENDING;
                case DbConstants.Status.Finished:
                    return (what == IconOrTip.ICON) ? WebConfigData.ICON_NOT_ACTIVE : WebConfigData.TIP_NOT_ACTIVE;
                case DbConstants.Status.Rejected:
                    return (what == IconOrTip.ICON) ? WebConfigData.ICON_REJECTED : WebConfigData.TIP_REJECTED;
                default:
                    return string.Empty;
            }
        }
    }
}
